from PyQt5 import QtWidgets,QtGui
from Ui_mainwindow import  Ui_MainWindow
from PyQt5.QtCore import Qt
from PyQt5.QtGui import QIcon,QBrush,QColor
import sys
import os,logging,time,configparser,openpyxl
import pandas as pd
from myqcombobox import MyQComboBox

cfg_file = "cfg.ini"
Version = "Excel检查工具 V3.0"

class MyWindowShow(QtWidgets.QMainWindow, Ui_MainWindow):

    def __init__(self):
        super(MyWindowShow, self).__init__()
        self.setupUi(self)
        self.setWindowTitle(Version)
        self.setWindowIcon(QIcon('Flap.ico'))
        self.log_init()
        logging.info("Version:"+Version)
        self.table_para.horizontalHeader().setSectionResizeMode(QtWidgets.QHeaderView.Stretch)
        # self.table_res.horizontalHeader().setSectionResizeMode(QtWidgets.QHeaderView.Stretch)
        self.btn_a.clicked.connect(self.choose_file_a)
        self.btn_b.clicked.connect(self.choose_file_b)
        self.btn_check_para.clicked.connect(self.check_para)
        self.btn_add.clicked.connect(self.add_para)
        self.btn_a.clicked.connect(self.fun_open_a)
        # 开始根据参数列表比对BOM
        self.btn_start_compare.clicked.connect(self.cmp)
        # 选中单元格
        self.table_res.cellClicked.connect(self.cell_detail)
        self.cfg = configparser.ConfigParser()
        self.cfg.read(cfg_file,'utf-8')
        self.file_a=''
        self.file_b=''

    
    def get_sheet(self,file_name,key):
        logging.info("File Name:"+file_name)
        wb = openpyxl.load_workbook(file_name)
        sheet_list = []
        for each in wb.sheetnames:
            if each.find(key)>-1:
                sheet_list.append(each)
        logging.info(sheet_list)
        wb.close()
        return sheet_list

    def choose_file_a(self):
        
        self.file_a,ftype = QtWidgets.QFileDialog.getOpenFileName(self,"请选择原始Excel文件",os.getcwd(),'Excel Files (*.xlsx)')
        logging.info(self.file_a)
        if self.file_a!='':
            self.file_a = self.file_a.replace("//","\\")
            logging.info("已选择原始文件:"+str(self.file_a))
            self.statusbar.showMessage("已选择原始文件:"+str(self.file_a))
            current_dir = os.path.split(self.file_a)[0]
            os.chdir(current_dir)
            self.lineEdit_a.setText(self.file_a)
            wb = openpyxl.load_workbook(self.file_a)
            self.cBox_sa.clear()
            self.cBox_sa.addItems(wb.sheetnames)
            wb.close()
        else:
            logging.info("没有选择正确文件")
            self.statusbar.showMessage("没有选择正确文件")

    def fun_open_a(self):
        logging.info("Open File A")

    def choose_file_b(self):
        self.file_b,ftype = QtWidgets.QFileDialog.getOpenFileName(self,"请选择原始Excel文件",os.getcwd(),'Excel Files (*.xlsx)')
        if self.file_b!='':
            self.file_b = self.file_b.replace("//","\\")
            logging.info("已选择目标文件:"+str(self.file_b))
            self.statusbar.showMessage("已选择目标文件:"+str(self.file_b))
            current_dir = os.path.split(self.file_b)[0]
            os.chdir(current_dir)
            self.lineEdit_b.setText(self.file_b)
            wb = openpyxl.load_workbook(self.file_b)
            self.cBox_sb.clear()
            self.cBox_sb.addItems(wb.sheetnames)
            wb.close()
        else:
            logging.info("没有选择正确文件")
            self.statusbar.showMessage("没有选择正确文件")
    
    def check_para(self):
        logging.info("开始比对文件...")
        if self.file_a!='' and self.file_b != '':
            try:
                logging.info("Try to load file_a")
                index = self.cfg["default"]["index_key"]
                sheet_a = self.cBox_sa.currentText()
                sheet_b = self.cBox_sb.currentText()
                # df = pd.read_excel(self.file_a,header=1,sheet_name=sheet_a,dtype=str)
                self.dfa = pd.read_excel(self.file_a,header=1,sheet_name=sheet_a,dtype=str)
                # dfa = self.df_trans(df)
                # dfa[Designator] = dfa[Designator].str.split(split_char)
                # self.dfa_exp = dfa.explode(Designator)
                # self.dfa_exp["check"] = False
                # self.dfa_exp = self.dfa_exp.reset_index()
                
                # 读取文件A列名称,转为列表增加到下拉框cBox_a中
                self.column_a = self.dfa.columns.to_list()
                self.cBox_a.clear()
                self.cBox_a.addItems(self.column_a)

                self.dfb = pd.read_excel(self.file_b,header=1,sheet_name=sheet_b,dtype=str)
                # dfb = self.df_trans(df)
                # dfb[Designator] = dfb[Designator].str.split(split_char)
                # self.dfb_exp = dfb.explode(Designator)
                # self.dfb_exp["check"] = False
                # self.dfb_exp = self.dfb_exp.reset_index()
                # 读取文件B列名称,转为列表增加到下拉框cBox_b中
                self.column_b = self.dfb.columns.to_list()
                self.cBox_b.clear()
                self.cBox_b.addItems(self.column_b)
                logging.info("This is file b exploded")
                self.para_list = []

                self.statusbar.showMessage("读取完成...")
                self.table_res.setRowCount(0)
            except OSError as reason:
                logging.info(reason)
                self.statusbar.showMessage(str(reason))
        else:
            self.statusbar.showMessage("请选择正确的文件...")


    def df_trans(self,df):
        '''
        DataFrame转换:
        1,df中不存在"存货编码"和"类别"两个字段就自行增加两个空列
        2,df中类别标记为"替代料"就将"位号","数量","序号"替换为主料参数,合并单元格中除了第一行之外读出来都是空
        3,删除位号中的空格和回车
        '''
        df = df.fillna("NULL")
        try:
            category = self.cfg["default"]["Category"]
            rep = self.cfg["default"]["Rep"]
            Designator = self.cfg["default"]["Designator"]
            Quantity = self.cfg["default"]["Quantity"]
            num = self.cfg["default"]["num"]
            index_key = self.cfg["default"]["index_key"]
            Part_number = self.cfg["default"]["Part_number"]
            Description = self.cfg["default"]["Description"]
            Manufacturer = self.cfg["default"]["Manufacturer"]
            # 原始文件没有类别就增加字段"类别"
            if (category in df.columns)==False:
                logging.info("BOM中不存在字段:%s"%category)
                df[category] = self.cfg["default"]["nan"]
            # 原始文件没有"存货编码"就增加字段"存货编码"    
            if (index_key in df.columns)==False:
                logging.info("BOM中不存在字段:%s"%index_key)
                df[index_key] = self.cfg["default"]["nan"]
            # 原始文件没有"生产厂家"就增加字段"生产厂家" 
            if (Manufacturer in df.columns)==False:
                logging.info("BOM中不存在字段:%s"%Manufacturer)
                df[Manufacturer] = self.cfg["default"]["nan"]
            # 华瑞BOM 摘取型号
            if (Part_number in df.columns)==False:
                logging.info("BOM中不存在字段:%s"%index_key)
                df[Part_number] = self.cfg["default"]["nan"]   
                ser = df[Description].str.split(" ")
                for each in ser.index:
                    ser.loc[each] = ser.loc[each][-1] if ser.loc[each]==ser.loc[each] else "NULL"
                df[Part_number] =ser
            if (Quantity in df.columns)==False:
                logging.info("BOM中不存在字段:%s"%Quantity)
                df[index_key] = 0
            if (Part_number in df.columns)==False:
                logging.info("BOM中不存在字段:%s"%Part_number)
                df[Part_number] = self.cfg["default"]["nan"]
                for each in df.index:
                    desc = df.loc[each,Description]
                    list1 =desc.split()
                    logging.info(desc)
                    if len(list1)>1:
                        df.loc[each,Part_number] = list1[-1]
                        df.loc[each,Manufacturer] = list1[-2]
            if True:
                # 删除空格
                for each in df.index:
                    df.loc[each,Designator] = df.loc[each,Designator].replace(" ","")

        except OSError as reason:
            logging.info("config error"+str(reason))
            category = "类别"
            rep = "替代料"
            Designator = "位号"
            Quantity = "用量"
            num = "序号"
        # 替代料的 序号,位号,数量复制主料信息
        for each in df.index:
            ser = df.loc[each]
            if ser[category]==rep:
                index_pre = each-1
                df.loc[each,Designator] = df.loc[index_pre,Designator]
                df.loc[each,Quantity] = df.loc[index_pre,Quantity]
                df.loc[each,num] = df.loc[index_pre,num]
            if ser[Designator][-1]==',':
                df.loc[each,Designator] = df.loc[each,Designator][:-1]
        # 位号删除空格和回车
        df[Designator] = df[Designator].str.replace("\n",',')
        df[Designator] = df[Designator].str.replace(" ",'')
        return df


    def add_para(self):

        para_a = self.cBox_a.currentText()
        para_b = self.cBox_b.currentText()
        logging.info("增加参数:File_A:%s,File_B:%s"%(para_a,para_b))
        para_info = [para_a,para_b,True]
        self.para_list.append(para_info)
        self.table_update(para_info)
        self.column_a.remove(para_a)
        self.column_b.remove(para_b)
        self.cBox_a.clear()
        self.cBox_b.clear()
        self.cBox_a.addItems(self.column_a)
        self.cBox_b.addItems(self.column_b)

    def cmp(self,):
        '''
        开始根据参数列表比对BOM
        '''
        self.res_column = ['序号','位号']
        for each in range(self.table_para.rowCount()-1):
            self.res_column.append(self.table_para.item(each+1,0).text())
        self.table_res.setColumnCount(len(self.res_column))
        Designator =self.cfg["default"]["Designator"]
        split_char = self.cfg["default"]["split_char"]
        self.column_trans()
        dfa = self.df_trans(self.dfa)
        dfa[Designator] = dfa[Designator].str.split(split_char)
        logging.info("This is file-a exploded")
        self.dfa_exp = dfa.explode(Designator)
        self.dfa_exp["check"] = False
        self.dfa_exp = self.dfa_exp.reset_index()

        dfb = self.df_trans(self.dfb)
        dfb[Designator] = dfb[Designator].str.split(split_char)
        self.dfb_exp = dfb.explode(Designator)
        self.dfb_exp["check"] = False
        self.dfb_exp = self.dfb_exp.reset_index()
        if True:
            logging.info(self.dfb_exp)
            logging.info(self.dfa_exp)
            self.dfa_exp.to_excel("dfa_exp.xlsx")
            self.dfb_exp.to_excel("dfb_exp.xlsx")
        num = self.cfg["default"]["num"]
        Quantity = self.cfg["default"]["Quantity"]
        Description = self.cfg["default"]["Description"]
        Manufacturer = self.cfg["default"]["Manufacturer"]
        Part_number = self.cfg["default"]["Part_number"]
        split_char = self.cfg["default"]["split_char"]
        category = self.cfg["default"]["Category"]
        yes = self.cfg["default"]["yes"]
        no = self.cfg["default"]["no"]
        self.text_msg.clear()
        counter = 0
        Designator_list = self.dfa_exp[Designator].tolist()+self.dfb_exp[Designator].tolist()
        d_unique_list = []
        [d_unique_list.append(x) for x in Designator_list if x not in d_unique_list]
        for each in d_unique_list:
            logging.info("load device:"+each)
            counter = counter+1
            res = [0,"位号",0,0,0,0,0,' ']
            # 判断出厂型号
            sql = "%s == '%s'"%(Designator,each)
            logging.info(sql)
            dfb_dev = self.dfb_exp.query(sql)
            dfa_dev = self.dfa_exp.query(sql)
            da_res = yes if dfa_dev.shape[0]>0 else no
            db_res = yes if dfb_dev.shape[0]>0 else no
            logging.info(dfb_dev)
            logging.info(dfa_dev)
            if dfa_dev.shape[0]>0 and dfb_dev.shape[0]>0:
                for serb in dfb_dev.iloc:
                    if dfa_dev.shape[0]==1:
                        for sera in dfa_dev.iloc:
                            res = self.cmp_ser(counter,each,sera,serb)
                            self.update_res(res)
                    else:
                        # 比较"位号"和"出厂型号"
                        sql="%s == '%s' and %s=='%s'"%(Designator,serb[Designator],Part_number,serb[Part_number])
                        logging.info(sql)
                        df_tmp = dfa_dev.query(sql)
                        if df_tmp.shape[0]>0:
                            for sera in df_tmp.iloc:
                                res = self.cmp_ser(counter,each,sera,serb)
                                self.update_res(res)
                        else:
                            res = [counter,each,no,no,no,no,no,'']
                            self.update_res(res)
            else:
                res = [counter,each,no,no,no,no,no,'']
                res[2] = "BOM A:%s,BOM B:%s"%(da_res,db_res)
                self.update_res(res)

    def column_trans(self):
        '''
        列名转换
        '''
        logging.info("dfb_exp开始列名转换...")
        rowcount = self.table_para.rowCount()
        colcount = self.table_para.columnCount()
        logging.info("参数表格共有%d行,%d列"%(rowcount,colcount))
        for each in range(rowcount-1):
            col_a = self.table_para.item(each+1,0).text()
            col_b = self.table_para.item(each+1,1).text()
            widget = self.table_para.cellWidget(each+1,2)
            if widget.isChecked():
                logging.info(col_a+'---'+col_b+"-->True")
                self.dfb = self.dfb.rename(columns={col_b:col_a})
                logging.info(self.dfb.columns)
            else:
                logging.info(col_a+'---'+col_b+"-->False")



    
    def cmp_ser(self,counter,each,sera,serb):
        res = [0,"位号",0,0,0,0,0,' ']
        yes = self.cfg["default"]["yes"]
        no = self.cfg["default"]["no"]
        Designator = self.cfg["default"]["Designator"]
        Quantity = self.cfg["default"]["Quantity"]
        Description = self.cfg["default"]["Description"]
        Manufacturer = self.cfg["default"]["Manufacturer"]
        Part_number = self.cfg["default"]["Part_number"]
        res[0] = counter
        res[1] = each
        res[2] = yes
        res[3] = yes if serb[Part_number]==sera[Part_number] else no
        res[4] = yes if serb[Manufacturer]==sera[Manufacturer] else no
        res[5] = yes if serb[Description]==sera[Description] else no
        res[6] = yes if serb[Quantity]==sera[Quantity] else no
        logging.info("%s<-->%s"%(sera[Part_number],serb[Part_number]))
        logging.info("%s<-->%s"%(sera[Manufacturer],serb[Manufacturer]))
        logging.info("%s<-->%s"%(sera[Description],serb[Description]))
        logging.info("%s<-->%s"%(sera[Quantity],serb[Quantity]))

        return res


    def cell_detail(self,row,column):
        self.text_msg.clear()
        logging.info("I clicked %d行,%d列;"%(row,column))
        # index = int(self.table_res.item(row,0).text())
        # serb = self.df_b.loc[index]
        # para_list = [self.cfg["default"]["num"],]
        # para_list.append(self.cfg["default"]["Name"])
        # para_list.append(self.cfg["default"]["Part_number"])
        # para_list.append(self.cfg["default"]["Designator"])
        # para_list.append(self.cfg["default"]["Quantity"])
        # para_list.append(self.cfg["default"]["Manufacturer"])
        # para_list.append(self.cfg["default"]["Description"])
        # self.text_msg.append("Item in file_b:")
        # message =''
        # for each in para_list:
        #     message = message+ "%s:%s\t"%(each,serb[each])
        # self.text_msg.append(message)
        # df = self.df_a.query("位号 =='%s'"%serb["位号"])
        # self.text_msg.append("Item in file_a:")
        # if df.shape[0]==0:
        #     self.text_msg.append(serb["位号"]+"在BOM A中不存在")
        # else:
        #     # BOM A 中找到符合条件的记录
        #     for each in df.index:
        #         ser = df.loc[each]
        #         message =''
        #         for each in para_list:
        #             message = message+ "%s:%s\t"%(each,ser[each])
        #         self.text_msg.append(message)

    def table_update(self,para_info):
        index = len(self.para_list)
        self.table_para.setRowCount(index+1)
        QItem = QtWidgets.QTableWidgetItem(str(para_info[0]))
        QItem.setTextAlignment(Qt.AlignCenter)
        self.table_para.setItem(index,0,QItem)
        QItem = QtWidgets.QTableWidgetItem(str(para_info[1]))
        QItem.setTextAlignment(Qt.AlignCenter)
        self.table_para.setItem(index,1,QItem)

        checkBox = QtWidgets.QCheckBox("选择")
        checkBox.setObjectName("checkBox%d"%index)
        checkBox.setChecked(para_info[2])
        checkBox.setStyleSheet('QCheckBox{margin:5px}')
        self.table_para.setCellWidget(index,2,checkBox)
        # self.table_para.resize(240,(index+2)*30)
        if len(self.column_a)==1:
            self.btn_add.setEnabled(False)
        else:
            self.btn_add.setEnabled(True)

    def update_res(self,res):
        logging.info("更新比对结果...")
        row_cur = self.table_res.rowCount()
        self.table_res.setRowCount(row_cur+1)
        row = row_cur
        column = 0
        yes = self.cfg["default"]["yes"]
        no = self.cfg["default"]["no"]
        for each in res:
            QItem = QtWidgets.QTableWidgetItem(str(each))
            if each==yes:
                QItem.setBackground(QBrush(QColor("green")))
            if no in str(each):
                QItem.setBackground(QBrush(QColor("red")))
            QItem.setTextAlignment(Qt.AlignCenter)
            self.table_res.setItem(row,column,QItem)
            column = column+1

    def log_init(self,):    
        if not(os.path.exists("LogFile")):
            os.mkdir("LogFile")
        time_str = time.strftime('%Y_%m%d_%H%M%S', time.localtime())
        log_file_name = ".\\LogFile\\"+time_str+".txt"
        logging.basicConfig(filename=log_file_name,format="%(asctime)s %(name)s:%(levelname)s-->%(message)s",level='INFO',encoding='utf-8')


if __name__ == "__main__":
    app = QtWidgets.QApplication(sys.argv)
    window = MyWindowShow()
    window.show()
    sys.exit(app.exec_())
